package com.lpf.testMyCat;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

import java.sql.DriverManager;
import java.sql.ResultSet;

public class MyCat_Mysql_Test {

	static Connection con = null; // 定义一个MYSQL链接对象
	static String insert_global = "INSERT INTO t_od_data_dictionary("
								+ "ID,"
								+ "CODE,"
								+ "NAME,"
								+ "EXTEND,"
								+ "SHOW_INDEX,"
								+ "PARENT_CODE,"
								+ "IS_BUILTIN,"
								+ "OPT_USERID,"
								+ "SUB_TIME,"
								+ "UPDATE_USERID,"
								+ "UPDATE_TIME,"
								+ "REMARK"
							+ ") "
							+ "VALUES "
								+ "("
									+ "'主键ID', "
									+ "'字典编码', "
									+ "'名称', "
									+ "'拓展信息（用于字典项的拓展信息配置）', "
									+ "'1', "
									+ "'父字典编码', "
									+ "'0', "
									+ "'提交人', "
									+ "'2015-03-08 21:27:45', "
									+ "'最后变更人', "
									+ "'2015-03-08 21:27:45', "
									+ "'备注');";
	
	static String insert = "INSERT INTO t_od_cust_order_valet("
			+ "ID,"
			+ "CUST_ORDER_ID,"
			+ "CUST_ORDER_NO,"
			+ "BE_VALET_BILLID,"
			+ "VALET_BILLID,"
			+ "CREATE_TIME,"
			+ "UPDATE_TIME,"
			+ "REMARK"
		+ ") "
		+ "VALUES "
			+ "("
				+ "'主键ID999', "
				+ "'客户订单ID', "
				+ "'客户订单编号', "
				+ "'999', "
				+ "'代客账号', "
				+ "'2015-03-08 21:27:45', "
				+ "'2015-03-08 21:27:45', "
				+ "'备注');";
	
	static String delete_global = "DELETE FROM t_od_data_dictionary WHERE id = '主键ID'";
	static String delete = "DELETE FROM t_od_cust_order_valet WHERE id = '主键ID999'";
	
	static String update_global = "UPDATE t_od_data_dictionary SET CODE = '字典编码2' WHERE id = '主键ID'";
	static String update = "UPDATE t_od_cust_order_valet SET CUST_ORDER_ID = '客户订单ID2' WHERE id = '主键ID999'";
	
	static String select_global = "SELECT * FROM t_od_data_dictionary";
	static String select = "SELECT * FROM t_od_cust_order_valet";
	
	public static void main(String arg[]) {
		// 获取数据连接，必须调用
		getCon();

		/**
		 * 增
		 */
		//inert(insert_global);
		//inert(insert);
		/**
		 * 删
		 */
		//delete(delete_global);
		//delete(delete);
		/**
		 * 改
		 */
		//update(update_global);
		//update(update);
		/**
		 * 查
		 */
		//select(select_global);
		select(select);
		// 测试操作某一个分库（分片）（MyCat注解）
		//select("/*!mycat: sql=select id from t_od_cust_order_valet where BE_VALET_BILLID=999 */" + select + "  where BE_VALET_BILLID=001");
	}

	public static void getCon() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance(); // MYSQL驱动
			con = (Connection) DriverManager.getConnection("jdbc:mysql://20.26.28.134:8066/DDZX", "root", "MySQL_0571##"); // 链接本地MYSQL
			//con = (Connection) DriverManager.getConnection("jdbc:mysql://127.0.0.1:8066/small", "root", "123456"); // 链接本地MYSQL
			System.out.println("-----------连接数据库成功----------");
		} catch (Exception e) {
			System.out.print("MYSQL ERROR:" + e.getMessage());
		}
	}

	public static void inert(String sql) {
		try {
			Statement stmt; // 创建声明
			stmt = (Statement) con.createStatement();
			// 新增一条数据
			int retu = stmt.executeUpdate(sql);
			System.out.println("插入数据，返回值：" + retu);
		} catch (Exception e) {
			System.out.print("MYSQL ERROR:" + e.getMessage());
		}
	}

	public static void delete(String sql) {
		try {
			Statement stmt; // 创建声明
			stmt = (Statement) con.createStatement();
			long deleteRes = stmt.executeUpdate(sql); // 如果为0则没有进行删除操作，如果大于0，则记录删除的条数
			System.out.println("删除数据，返回值：" + deleteRes);
		} catch (Exception e) {
			System.out.print("MYSQL ERROR:" + e.getMessage());
		}
	}

	public static void update(String sql) {
		try {
			Statement stmt; // 创建声明
			stmt = (Statement) con.createStatement();
			long updateRes = stmt.executeUpdate(sql);
			System.out.print("更新数据，返回值：" + updateRes);
		} catch (Exception e) {
			System.out.print("MYSQL ERROR:" + e.getMessage());
		}
	}

	public static void select(String sql) {
		try {

			Statement stmt; // 创建声明
			stmt = (Statement) con.createStatement();
			ResultSet selectRes = stmt.executeQuery(sql);
			while (selectRes.next()) { // 循环输出结果集
				String id = selectRes.getString("ID");
				String updateTime = selectRes.getString("UPDATE_TIME");
				System.out.println("id:" + id + " updateTime:" + updateTime);
			}

		} catch (Exception e) {
			System.out.print("MYSQL ERROR:" + e.getMessage());
		}
	}
}